import re
from enum import Enum
from sqlalchemy import Column, Integer, String, DateTime, Text, Float, func, SmallInteger, UniqueConstraint, \
    Enum as SqlEnum, ForeignKey, Table, Date, event, text
from sqlalchemy.dialects.mysql import TINYINT, INTEGER
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import validates, relationship, Session

from model.db.well_model import TbRockCore, TbCoreData
from model.params import RoleNameEnum

Base = declarative_base()


class TbExperimentWater(Base):
    __tablename__ = 'tb_experiment_water'
    __table_args__ = {'comment': '水敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='水敏指数')
    remark = Column(String(255), comment='备注')


class TbExperimentWaterData(Base):
    __tablename__ = 'tb_experiment_water_data'
    __table_args__ = {'comment': '水敏实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    inject_fluid = Column(String(255), comment='注入流体')
    Inject_volume = Column(INTEGER(30), comment='注入体积倍数')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='损害率')


@event.listens_for(TbExperimentWater, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentWaterData.__table__.delete().where(TbExperimentWaterData.experiment_ID == target.id)
    )


class TbExperimentWaterBlocking(Base):
    __tablename__ = 'tb_experiment_waterBlock'
    __table_args__ = {'comment': '水锁实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11), unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='水锁指数')
    remark = Column(String(255), comment='备注')


class TbExperimentWaterBlockingData(Base):
    __tablename__ = 'tb_experiment_waterBlock_data'
    __table_args__ = {'comment': '水锁实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    mobile_water_saturation = Column(String(30), comment='可动水饱和度')
    mobile_water_damage = Column(String(30), comment='可动水伤害率')
    immobile_water_saturation = Column(String(30), comment='不可动水饱和度')
    immobile_water_damage = Column(String(30), comment='不可动水伤害率')
    permeability = Column(String(30), comment='渗透率')
    damage_ratio = Column(String(30), comment='损害率')

@event.listens_for(TbExperimentWaterBlocking, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentWaterBlockingData.__table__.delete().where(TbExperimentWaterBlockingData.experiment_ID == target.id)
    )





class TbExperimentSalt(Base):
    __tablename__ = 'tb_experiment_salt'
    __table_args__ = {'comment': '盐敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='盐敏伤害率')
    remark = Column(String(255), comment='备注')


class TbExperimentSaltData(Base):
    __tablename__ = 'tb_experiment_salt_data'
    __table_args__ = {'comment': '盐敏实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    remark = Column(String(255), comment='备注')
    critical_mineral_concentration = Column(Float(30), comment='临界矿化度')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='损害率')

@event.listens_for(TbExperimentSalt, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentSaltData.__table__.delete().where(TbExperimentSaltData.experiment_ID == target.id)
    )
class TbExperimentAcid(Base):
    __tablename__ = 'tb_experiment_acid'
    __table_args__ = {'comment': '酸敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='酸敏伤害率')
    remark = Column(String(255), comment='备注')


class TbExperimentAcidData(Base):
    __tablename__ = 'tb_experiment_acid_data'
    __table_args__ = {'comment': '酸敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    acid_concentration = Column(Float(30), comment='盐酸浓度')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='酸敏伤害率')
    remark = Column(String(255), comment='备注')


@event.listens_for(TbExperimentAcid, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentAcidData.__table__.delete().where(TbExperimentAcidData.experiment_ID == target.id)
    )
class TbExperimentAlkaline(Base):
    __tablename__ = 'tb_experiment_alkaline'
    __table_args__ = {'comment': '碱敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='碱敏指数')
    remark = Column(String(255), comment='备注')


class TbExperimentAlkalineData(Base):
    __tablename__ = 'tb_experiment_alkaline_data'
    __table_args__ = {'comment': '碱敏实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    PH = Column(Float(30), comment='PH')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='损害率')

@event.listens_for(TbExperimentAlkaline, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentAlkalineData.__table__.delete().where(TbExperimentAlkalineData.experiment_ID == target.id)
    )

class TbExperimentSpeed(Base):
    __tablename__ = 'tb_experiment_speed'
    __table_args__ = {'comment': '速敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='速敏指数')
    remark = Column(String(255), comment='备注')


class TbExperimentSpeedData(Base):
    __tablename__ = 'tb_experiment_speed_data'
    __table_args__ = {'comment': '速敏实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    speed = Column(Float(30), comment='流速')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='损害率')


@event.listens_for(TbExperimentSpeed, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentSpeedData.__table__.delete().where(TbExperimentSpeedData.experiment_ID == target.id)
    )

class TbExperimentPressure(Base):
    __tablename__ = 'tb_experiment_pressure'
    __table_args__ = {'comment': '压敏实验评价'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    Core_ID = Column(INTEGER(11),unique=True, comment='所属岩芯ID')
    experiment_time = Column(DateTime, comment='实验日期')
    index = Column(Float(30), comment='压敏指数')
    remark = Column(String(255), comment='备注')


class TbExperimentPressureData(Base):
    __tablename__ = 'tb_experiment_pressure_data'
    __table_args__ = {'comment': '速敏实验数据'}
    id = Column(INTEGER(11), primary_key=True, comment='ID')
    experiment_ID = Column(INTEGER(30), nullable=False, comment='实验ID')
    pressure = Column(Float(30), comment='净围压')
    permeability = Column(Float(30), comment='渗透率')
    damage_ratio = Column(Float(30), comment='损害率')


@event.listens_for(TbExperimentPressure, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbExperimentPressureData.__table__.delete().where(TbExperimentPressureData.experiment_ID == target.id)
    )

class TbExperimentModelInputParam(Base):
    __tablename__ = 'tb_experiment_model_inputParam'
    __table_args__ = {'comment': '预测模型输入参数集表'}
    Model_ID = Column(String(50), primary_key=True, nullable=False, comment='模型ID')
    Parameter_ID = Column(INTEGER, primary_key=True, nullable=False, comment='参数ID')
    Parameter_Relation = Column(String(50), comment='参数(非)线性关系')


class TbExperimentModelOutputParam(Base):
    __tablename__ = 'tb_experiment_model_outputParam'
    __table_args__ = {'comment': '预测模型输出参数集表'}
    Model_ID = Column(String(50), primary_key=True, nullable=False, comment='模型ID')
    Parameter_ID = Column(INTEGER, primary_key=True, nullable=False, comment='参数ID')


class TbExperimentModelTrainDatasets(Base):
    __tablename__ = 'tb_experiment_model_trainData'
    __table_args__ = {'comment': '预测模型训练数集'}
    Model_ID = Column(String(50), primary_key=True, nullable=False, comment='模型ID')
    Core_ID = Column(String(50), primary_key=True, nullable=False, comment='岩芯ID')


class TbExperimentTestDatasets(Base):
    __tablename__ = 'tb_experiment_model_testData'
    __table_args__ = {'comment': '预测模型测试数据集'}
    Model_ID = Column(String(50), primary_key=True, nullable=False, comment='模型ID')
    Core_ID = Column(String(50), primary_key=True, nullable=False, comment='岩芯ID')


class TbExperimentPredictDatasets(Base):
    __tablename__ = 'tb_experiment_model_predictData'
    __table_args__ = {'comment': '模型预测数据集'}
    Model_ID = Column(String(50), primary_key=True, nullable=False, comment='模型ID')
    Core_ID = Column(String(50), primary_key=True, nullable=False, comment='岩芯ID')


class TbExperimentModell(Base):
    __tablename__ = 'tb_experiment_model'
    __table_args__ = {'comment': '预测模型表'}
    Model_ID = Column(INTEGER, primary_key=True, nullable=False, comment='模型ID')
    Model_Name = Column(String(50), unique=True, nullable=False, comment='模型名称')
    Creator_User = Column(INTEGER(50), nullable=False, comment='创建人')
    Model_Type = Column(INTEGER, default=0, comment="模型类型")
    Sensitivity_Type = Column(INTEGER, default=0, comment="敏感性类型")
    Model_Description = Column(String(50), comment='模型简述')
    Create_DateTime = Column(DateTime, comment='创建时间', default=func.now())
    Update_DateTime = Column(DateTime, comment='修改时间', onupdate=func.now())


@event.listens_for(TbExperimentModell, 'after_insert')
def create_experiment_model_structure(mapper, connection, target):
    # 创建BP模型时，同时创建结构参数记录
    if target.Model_Type == 1:
        session = Session(bind=connection)
        new_model_id = target.Model_ID
        struct = session.query(TbExperimentModelsStructure).filter_by(Model_ID=new_model_id).first()
        if struct is None:
            structure_record = TbExperimentModelsStructure(Model_ID=new_model_id)
            session.add(structure_record)
            session.commit()
    if target.Model_Type == 2:
        session = Session(bind=connection)
        new_model_id = target.Model_ID
        struct = session.query(TbExperimentSOMModelsStructure).filter_by(Model_ID=new_model_id).first()
        if struct is None:
            structure_record = TbExperimentSOMModelsStructure(Model_ID=new_model_id)
            session.add(structure_record)
            session.commit()


@event.listens_for(TbExperimentModell, 'before_delete')
def create_experiment_model_structure(mapper, connection, target):
    # 删除BP模型时，同时删除结构参数记录
    if target.Model_Type == 1:
        session = Session(bind=connection)
        model_structure = session.query(TbExperimentModelsStructure).filter(
            TbExperimentModelsStructure.Model_ID == target.Model_ID).first()
        if model_structure is not None:
            session.delete(model_structure)
            session.commit()
    if target.Model_Type == 2:
        session = Session(bind=connection)
        model_structure = session.query(TbExperimentSOMModelsStructure).filter(
            TbExperimentSOMModelsStructure.Model_ID == target.Model_ID).first()
        if model_structure is not None:
            session.delete(model_structure)
            session.commit()


class TbExperimentModelsStructure(Base):
    __tablename__ = 'tb_nn_model_structure'
    __table_args__ = {'comment': 'BP模型结构参数'}
    Model_ID = Column(INTEGER, primary_key=True, nullable=False, comment='模型ID')
    InputLayer_CoreNum = Column(INTEGER(30), comment='输入神经元数')
    HideLayer_Num = Column(INTEGER(30), default=1, comment='隐藏层数')
    HideLayer_CoreNum = Column(INTEGER(30), default=15, comment='隐藏层神经元数')
    Output_Layer_CoreNum = Column(INTEGER(30), comment='输出层神经元数')
    HideLayer_Active = Column(String(30), default='relu', comment='激活函数类型')
    Learn_Rate = Column(Float(30), default=0.1, comment='学习率')
    Target_error = Column(Float(30), default=0.001, comment='目标误差')
    MaxEpoch = Column(INTEGER(100), default=800, comment='最大迭代次数')
    Real_Epoch = Column(INTEGER(100), comment='实际迭代次数')
    Result_Error = Column(Float(30), comment='实际迭代最终误差')
    Epoch_Error = Column(Text, comment='训练误差')
    Epoch_Duration = Column(Float(30), comment='实际迭代时长')
    WeightFileName = Column(String(225), comment='权重文件名')
    Create_DateTime = Column(DateTime, comment='创建时间', default=func.now(), onupdate=func.now())


class TbExperimentSOMModelsStructure(Base):
    __tablename__ = 'tb_som_model_structure'
    __table_args__ = {'comment': 'BP模型结构参数'}
    Model_ID = Column(INTEGER, primary_key=True, nullable=False, comment='模型ID')
    Formula = Column(String(225), comment='拟合公式')
    Result_Error = Column(Float(30), comment='实际迭代最终误差')
    Epoch_Duration = Column(Float(30), comment='实际迭代时长')
    Create_DateTime = Column(DateTime, comment='创建时间', default=func.now(), onupdate=func.now())


@event.listens_for(TbRockCore, 'after_delete')
def delete_related_core_data(mapper, connection, target):
    connection.execute(
        TbCoreData.__table__.delete().where(TbCoreData.Core_ID == target.Core_ID)
    )
